<?php

class M_finance extends MY_Model{
	
	public function __construct()
	{
		parent::__construct();
	}	
	
	public function view_record(){
		$query = $this->db->get('profiles');	
		return $query->result();
	}
	
	public function get_field(){
		return $result = $this->db->list_fields('profiles');	
	}
	
	public function get_daily_payment_records($now)
	{
		$date = DateTime::createFromFormat('j-M-Y',$now);

		$this->db->get('student_payment_records')
				 ->where('spr_is_deleted',0)
				 ->where('spr_schoolyear_id',$this->c_sy->id)
				 ->where('spr_payment_date',$date->format(''));
	}
	
	
	//total reports of payments for the current month daily
	public function daily_month_reports($month,$year)
	{
		$days =	cal_days_in_month(CAL_GREGORIAN,$month,date('Y'));
		
		for($x=1;$x<=$days;$x++)
		{
			$result = $this->_get_daily_reports($month,$x,$year);
			if(is_object($result))
			{
				$values[$x] = 'total payments:<br><strong>&#8369; '.$result->value.'</strong> <br><br><a href="'.$result->link.'" class="confirm btn btn-mini" title="Are you sure you want to Download File?">Download Excel</a>';
			}else{
				$values[$x] = $result ;
			}
		}
		return $values;
	}
	

	//Month reports with daily totals start
	// used in finance calendar
	private function _get_daily_reports($month,$day,$year)
	{
		$query = $this->db->select('sum(spr_ammt_paid) as totals')
						  ->where('spr_is_deleted',0)
 						  ->where('spr_schoolyear_id',$this->c_sy->id)
						  ->where('MONTH(spr_payment_date)',$month)
						  ->where('YEAR(spr_payment_date)',date('Y'))
						  ->where('DAY(spr_payment_date)',$day)
						  ->limit(1)
						  ->get('student_payment_records');
						  
		if($query->num_rows() >= 1)
		{
			$total = $query->row()->totals; 
			
			if($total == NULL)
			{
				return '_____';
			}else{
				$ci =& get_instance();

				$total = number_format($total,2,'.',',');
				$auth = $ci->authlink->hash_make($year);
				$link = site_url("mdr/{$year}/{$month}/{$day}/{$auth}");
				return (object)array('value'=>$total,'month'=>$month,'day'=>$day,'link'=>$link);
			}
		}else{
			return 'Error';
		}
	}
	
	//============================================
	public function daily_month_reports_with_names_for_report($month,$year)
	{
		$days =	cal_days_in_month(CAL_GREGORIAN,$month,date('Y'));
		
		for($x=1;$x<=$days;$x++)
		{
			$result = $this->_zzzzzzz($month,$x,$year);
			if(is_object($result))
			{
				$values[$x] = $result->value;
			}else{
				$values[$x] = $result ;
			}
		}
		return $values;
	}
	
	private function _zzzzzzz($month,$day,$year)
	{
		
		$query = $this->db->select('spr_ammt_paid as amt_paid,
											concat(p.lastname," ",p.firstname," ,",p.middlename) as fullname,
											l.level_desc as grade_level',FALSE)
								  ->join('enrollments e','student_payment_records.spr_enrollment_id = e.e_id')
								  ->join('profiles p','p.profile_id = e.e_profile_id')
								  ->join('levels l','l.level_id = e.e_grade_level')
								  ->where('spr_is_deleted',0)
								  ->where('spr_schoolyear_id',$this->c_sy->id)
								  ->where('MONTH(spr_payment_date)',$month)
								  ->where('YEAR(spr_payment_date)',date('Y'))
								  ->where('DAY(spr_payment_date)',$day)
								  ->where('e.e_archived',0)
								  ->where('e.verified',1)
								  ->where('e.registrar_approved',1)
								  ->get('student_payment_records');		
		
						  
		if($query->num_rows() >= 1)
		{
			return $query->result();
		}else{
			return NULL;
		}
	}
	
	//============================================
	
	private function _get_daily_reports_with_names($month,$day,$year)
	{
		$query = $this->db->select('sum(spr_ammt_paid) as totals')
						  ->where('spr_is_deleted',0)
 						  ->where('spr_schoolyear_id',$this->c_sy->id)
						  ->where('MONTH(spr_payment_date)',$month)
						  ->where('YEAR(spr_payment_date)',date('Y'))
						  ->where('DAY(spr_payment_date)',$day)
						  ->limit(1)
						  ->get('student_payment_records');
						  
		if($query->num_rows() >= 1)
		{
			$total = $query->row()->totals; 
			
			if($total == NULL)
			{
				return '_____';
			}else{
				$ci =& get_instance();

				$total = number_format($total,2,'.',',');
				$auth = $ci->authlink->hash_make($year);
				$link = site_url("mdr/{$year}/{$month}/{$day}/{$auth}");
				return (object)array('value'=>$total,'month'=>$month,'day'=>$day,'link'=>$link);
			}
		}else{
			return 'Error';
		}
	}
	
	

	//used in generation of reports for monthly with names
	
	public function monthly_daily_reports_with_names($year,$month,$day)
	{
		$query = $this->db->select('spr_ammt_paid as amt_paid,
									concat(p.lastname," ",p.firstname," ,",p.middlename) as fullname,
									l.level_desc as grade_level',FALSE)
						  ->join('enrollments e','student_payment_records.spr_enrollment_id = e.e_id')
						  ->join('profiles p','p.profile_id = e.e_profile_id')
						  ->join('levels l','l.level_id = e.e_grade_level')
						  ->where('spr_is_deleted',0)
						  ->where('spr_schoolyear_id',$this->c_sy->id)
						  ->where('MONTH(spr_payment_date)',$month)
						  ->where('YEAR(spr_payment_date)',date('Y'))
						  ->where('DAY(spr_payment_date)',$day)
						  ->where('e.e_archived',0)
						  ->where('e.verified',1)
						  ->where('e.registrar_approved',1)
						  ->get('student_payment_records');
							  
		return $query->num_rows() >=1 ? $query->result() : FALSE;
	}
	//Month reports with daily totals end
	
	public function monthly_reports_with_names($year,$month,$day)
	{
		$query = $this->db->select('spr_ammt_paid as amt_paid,
									concat(p.lastname," ",p.firstname," ,",p.middlename) as fullname,
									l.level_desc as grade_level',FALSE)
						  ->join('enrollments e','student_payment_records.spr_enrollment_id = e.e_id')
						  ->join('profiles p','p.profile_id = e.e_profile_id')
						  ->join('levels l','l.level_id = e.e_grade_level')
						  ->where('spr_is_deleted',0)
						  ->where('spr_schoolyear_id',$this->c_sy->id)
						  ->where('MONTH(spr_payment_date)',$month)
						  ->where('YEAR(spr_payment_date)',date('Y'))
						  ->where('DAY(spr_payment_date)',$day)
						  ->where('e.e_archived',0)
						  ->where('e.verified',1)
						  ->where('e.registrar_approved',1)
						  ->get('student_payment_records');
							  
		return $query->num_rows() >=1 ? $query->result() : FALSE;
	}
	
	public function yearly_schoolyear_reports($id)
	{	
		$ci =& get_instance();
		$ci->load->model('M_school_years','msy');
		$month_num = 11;
		$month_nam = array('January','Febuary','March','April','May','June','July','August','September','October','November','December');
		
		if($id > 0)
		{
			$syd = $ci->msy->get_all_school_year($id);
			$status = 'one';
			for($x=0;$x<=$month_num;$x++)
			{
				$data[$syd[0]->sy_from.'-'.$syd[0]->sy_to][$month_nam[$x]] = $this->_get_yearly_reports($x+1,$syd[0]->sy_from,$syd[0]->sy_to);
			}
		}else
		{
			$syd = $ci->msy->get_all_school_year();
			$status = 'all';
			foreach($syd as $k=> $v)
			{
				$sy = $v->sy_from.'-'.$v->sy_to;
				for($x=0;$x<=$month_num;$x++)
				{
					$data[$sy][$month_nam[$x]] = $this->_get_yearly_reports($x+1,$v->sy_from,$v->sy_to);
				}
			}
		}
		
		return (object)array('status'=>$status,'values'=>$data);

	}
	
	private function _get_yearly_reports($month_number,$year1,$year2)
	{
		$query=	$this->db->select('sum(spr_ammt_paid) as payments')
					 ->where('month(spr_payment_date)',$month_number)
					 ->where('spr_is_deleted',0)
					 ->where('spr_schoolyear_id',$this->c_sy->id)
					 ->where('year(spr_payment_date)',$year1)
					 ->or_where('year(spr_payment_date)',$year2)
					 ->get('student_payment_records');
		
		return $query->row()->payments == NULL ? NULL : $query->row()->payments;
	}
}
